﻿using AutoMapper;
using Dapper;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Drawing.Charts;
using DocumentFormat.OpenXml.Math;
using DocumentFormat.OpenXml.Office2010.Excel;
using DocumentFormat.OpenXml.Office2013.Drawing.ChartStyle;
using DocumentFormat.OpenXml.Spreadsheet;
using Hangfire;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Repository.Extensions;
using housingsafety.server.Svcs;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Npgsql;
using Npoi.Mapper;
using NPOI.HPSF;
using NPOI.POIFS.Crypt.Dsig;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Org.BouncyCastle.Asn1.Icao;
using Org.BouncyCastle.Asn1.Ocsp;
using Quartz.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using static housingsafety.server.Controllers.HouseMngController;
using static housingsafety.server.Controllers.SampleTaskController;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 大屏基本信息 展示
    /// </summary>
    [AllowAnonymous]
    public class HouseHcController : BaseController<HouseHcController>
    {
        public IConfiguration Configuration { get; }
        private readonly IRepository<ZmVillageFireSafety> _villageFireSafetyRepository;
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly IStorageProvider _storageProvider;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmBuildings> _buildingsRepository;
        private readonly IRepository<ZmBuildingsLayer> _buildingsLayerRepository;
        private readonly IRepository<ZmUpdateLog> _zmupdateRepository;
        private readonly IRepository<ZmUpdateLog> _zmupdate;
        private readonly IRepository<ZmFileAttach> _fileAttachRepository;
        private readonly IRepository<ZmPlaceCommercial> _placeCommercialRepository;
        private readonly IRepository<ZmPlaceResident> _placeResidentRepository;
        private readonly IOptions<StaticDataConf> _staticVtOptions;
        private readonly IRepository<ZmDepartment> _departmentRepository;
        private readonly IRepository<RiskCensusHouseYhpc> _riskHouseRepository;
        private readonly IRepository<RiskCensusYhpcUpldFile> _riskHouseFileRepository;
        private readonly IRepository<ZmPlaceResidentCheck> _placeResidentCheckRepository;
        private readonly IRepository<ZmPlaceCommercialCheck> _placeCommercialCheckRepository;
        private readonly IRepository<ZmYtPhoto> _zmYtPhotoRepository;
        private readonly IDbTransaction _transaction;

        private readonly Microsoft.AspNetCore.Http.Features.FormOptions _defaultFormOptions = new Microsoft.AspNetCore.Http.Features.FormOptions();
        private readonly string[] _permittedExtensions = { ".mp3", ".mp4", ".jpg", ".jpeg", ".png", ".geojson", ".doc", ".docx" };

        private static object _lock = new object();

        public HouseHcController(IStorageProvider storageProvider
            , IRepository<ZmDepartment> departmentRepository
            , IRepository<ZmUser> userRepository
              , IRepository<ZmVillageFireSafety> villageFireSafetyRepository
              , IRepository<ZmBuildings> buildingsRepository
            , IRepository<ZmBuildingsLayer> buildingsLayerRepository
            , IRepository<ZmFileAttach> fileAttachRepository
            , IRepository<ZmPlaceCommercial> placeCommercialRepository
            , IRepository<ZmPlaceResident> placeResidentRepository
            , IRepository<RiskCensusHouseYhpc> riskHouseRepository
            , IRepository<RiskCensusYhpcUpldFile> riskHouseFileRepository
            , IRepository<ZmPlaceResidentCheck> placeResidentCheckRepository
            , IRepository<ZmPlaceCommercialCheck> placeCommercialCheckRepository
            , IRepository<ZmYtPhoto> zmYtPhotoRepository
            , IMapper mapper, ICacheProvider cacheProvider, ZmDbContext dbContext
            , IOptions<StaticDataConf> staticVtOptions
            , ILogger<HouseHcController> logger,
            IConfiguration configuration
            , IRepository<ZmSystemLog> systemLogRepository, IRepository<ZmUpdateLog> zmupdateRepository) : base(logger, systemLogRepository)
        {
            _zmupdateRepository = zmupdateRepository;
            _storageProvider = storageProvider;
            _mapper = mapper;
            _dbContext = dbContext;
            _cache = cacheProvider.CreateCache();
            _zmYtPhotoRepository = zmYtPhotoRepository;
            _userRepository = userRepository;
            _buildingsRepository = buildingsRepository;
            _buildingsLayerRepository = buildingsLayerRepository;
            _fileAttachRepository = fileAttachRepository;
            _placeCommercialRepository = placeCommercialRepository;
            _placeResidentRepository = placeResidentRepository;
            _staticVtOptions = staticVtOptions;
            _departmentRepository = departmentRepository;
            _riskHouseRepository = riskHouseRepository;
            _riskHouseFileRepository = riskHouseFileRepository;
            _placeResidentCheckRepository = placeResidentCheckRepository;
            _placeCommercialCheckRepository = placeCommercialCheckRepository;
            Configuration = configuration;
            _villageFireSafetyRepository = villageFireSafetyRepository;
            _zmupdateRepository = zmupdateRepository;

        }

        List<string> stringList = new List<string> { "栋", "栋", "处", "处" };
      
  


        /// <summary>
        /// 核查统计分析————根据行政区划统计进展情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("hc/evolvestatic/staticlist")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<BaseInfoStaticDto>>), 200)]
        public RespRet<Page<BuildingPcProgree>> EvolvestaticList([FromQuery] PageProgressRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<BuildingPcProgree>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<BuildingPcProgree>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<BuildingPcProgree>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq xzq = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            string strWhere = "  ";
            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)
            {
                strWhere += " and xzq='" + xzq.RegionName + "'";
            }
            else
            {
                string xzqdm = xzq.RegionCode.Substring(0, 6);
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            string floor = "";
            if (!string.IsNullOrEmpty(pageRequest.cs) && pageRequest.cs != "0") 
            {
                floor = " cs in ("+ pageRequest.cs + ") ";
            }
            string sql = "select ROW_NUMBER() OVER ()index ,*,case when yhcount=0 and zgcount=0 then '100.00%' else  concat( CASE " +
                "   WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00   ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)  END, '%') end zgl from " +
                " hcqkgqtext('"+ pageRequest.startTime + "'::timestamp, '"+ pageRequest.endTime+ " 23:59:59'::timestamp, "+ pageRequest.roletype+ ");";
            //string sql = "select ROW_NUMBER() OVER ()index ,*,case when yhcount=0 and zgcount=0 then '100.00%' else  concat(\r\n        CASE\r\n            WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00\r\n            ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)\r\n        END, '%') end zgl from hcqkgqtext5('" + pageRequest.startTime + "','" + pageRequest.endTime + " 23:59:59','"+pageRequest.roletype+"') where 1=1 " + strWhere + " order by xzqdm";
            var list = _dbContext.Database.GetDbConnection().Query<BuildingPcProgree>(sql).ToList();
            int count = list.Count;

            var pageData = new Page<BuildingPcProgree>(list
                , pageRequest.PageIndex + 1, pageRequest.Limit, count);
            return new RespRet<Page<BuildingPcProgree>>(true, "Ok", pageData);
        }
        /// <summary>
        /// 核查统计分析————根据行政区划统计进展情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("hc/evolvestatic/staticlistNew")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<BaseInfoStaticDto>>), 200)]
        public RespRet<Page<BuildingPcProgree>> EvolvestaticList_new([FromQuery] PageProgressRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<BuildingPcProgree>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<BuildingPcProgree>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<BuildingPcProgree>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq xzq = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            string strWhere = "  ";
            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)
            {
                strWhere += " and xzq='" + xzq.RegionName + "'";
            }
            else
            {
                string xzqdm = xzq.RegionCode.Substring(0, 6);
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            string floor = "";
            if (!string.IsNullOrEmpty(pageRequest.cs) && pageRequest.cs != "0" && pageRequest.cs != "null")
            {
                floor = " cs in (" + pageRequest.cs + ") ";
            }
            string toDay = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(pageRequest.endTime))
            {
                DateTime endData = Convert.ToDateTime(pageRequest.endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
                if (Convert.ToDateTime(pageRequest.endTime) > DateTime.Now)
                {
                    table_fwzt = "zm_building_fwzt_" + DateTime.Now.Year;
                }
            }
            else
            {
                DateTime endData = Convert.ToDateTime(toDay);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = "select ROW_NUMBER() OVER ()index ,*,case when yhcount=0 and zgcount=0 then '100.00%' else  concat( CASE " +
                "   WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00   ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)  END, '%') end zgl from " +
                " hcqkstatic('" + pageRequest.startTime + "'::timestamp, '" + pageRequest.endTime + " 23:59:59'::timestamp, " + pageRequest.roletype + ", '" + pageRequest.nickname + "', '" + floor + "','"+table_fwzt+"')  where 1=1 "+strWhere;
            //string sql = "select ROW_NUMBER() OVER ()index ,*,case when yhcount=0 and zgcount=0 then '100.00%' else  concat(\r\n        CASE\r\n            WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00\r\n            ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)\r\n        END, '%') end zgl from hcqkgqtext5('" + pageRequest.startTime + "','" + pageRequest.endTime + " 23:59:59','"+pageRequest.roletype+"') where 1=1 " + strWhere + " order by xzqdm";
            var list = _dbContext.Database.GetDbConnection().Query<BuildingPcProgree>(sql).ToList();
            int count = list.Count;

            var pageData = new Page<BuildingPcProgree>(list
                , pageRequest.PageIndex + 1, pageRequest.Limit, count);
            return new RespRet<Page<BuildingPcProgree>>(true, "Ok", pageData);
        }
        /// <summary>
        /// 统计分析————根据乡镇统计进展情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("hc/evolvestatic/Townshipstaticlist")]
        public RespRet<Page<object>> Townshipstaticlist([FromQuery] string xzq, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string roletype)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";
            if (!string.IsNullOrEmpty(xzq))
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and xzq ='" + xzq + "'";
                }
                else if (user.RoleType == 3)
                {
                    strWhere += " and xzq='" + model.RegionName + "'";
                }
                else
                {
                    string towndm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        towndm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + towndm + "'";
                }
            }

            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = DateTime.Now.ToShortDateString();
                endTime = DateTime.Now.ToShortDateString();
            }

            if (string.IsNullOrEmpty(roletype)) 
            {
                roletype = "4";
            }

            string sql = "select ROW_NUMBER() OVER ()index,*,case when yhcount=0 and zgcount=0 then '100.00%' else  concat(\r\n        CASE\r\n            WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00\r\n            ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)\r\n        END, '%') end zgl  from hcqkxztext('" + startTime + "','" + endTime + "','"+ roletype + "') where 1 = 1   " + strWhere + " order by xzqdm asc  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = "select count(1) cnt from  hcqkxztext('" + startTime + "','" + endTime + "','"+ roletype + "') where 1 = 1   " + strWhere + " ";
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 统计分析————根据核查村级统计进展情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("hc/evolvestatic/Villagestaticlist")]
        public RespRet<Page<object>> Villagestaticlist([FromQuery] string xzq, [FromQuery] string town, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string roletype)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }


            string strWhere = "";
            if (!string.IsNullOrEmpty(xzq + ""))
            {
                strWhere += "and xzq ='" + xzq + "'";
            }
            if (!string.IsNullOrEmpty(town + ""))
            {
                strWhere += "and town ='" + town + "'";
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = DateTime.Now.ToShortDateString();
                endTime = DateTime.Now.ToShortDateString();
            }
            if (string.IsNullOrEmpty(roletype))
            {
                roletype = "4";
            }
            string sql = "select ROW_NUMBER() OVER ()index ,* ,case when yhcount=0 and zgcount=0 then '100.00%' else  concat(\r\n        CASE\r\n            WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00\r\n            ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)\r\n        END, '%') end zgl from hcqkcjtext('" + startTime + "','" + endTime + "','"+ roletype + "')  where 1=1 " + strWhere + " order by xzqdm,towndm asc ,villagedm asc limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
               || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
               || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = "select count(1) cnt from hcqkcjtext('" + startTime + "','" + endTime + "','"+ roletype + "')  where 1=1 " + strWhere + " ";
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });
        }


        /// <summary>
        /// 核查工作进展统计
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("hc/evolvestatic/villagePostStatic")]
        public RespRet<Page<object>> villagePostStatic([FromQuery] int pageIndex, [FromQuery] int limit, [FromQuery] string districts, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked, [FromQuery] string roletype)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            string strWhere = "  ";

            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)  //区级
            {
                strWhere += " and xzqdm='" + model.RegionCode + "'";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and villagedm='" + model.RegionCode + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(towndm))
            {
                strWhere += " and towndm='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and villagedm='" + villagedm + "'";
            }



            //行政区划
            if (!string.IsNullOrEmpty(districts))
            {
                var districtCodeWhere = Utilitys.GetLikeSqlWhere(JsonConvert.SerializeObject(districts), "");
                if (!string.IsNullOrEmpty(districtCodeWhere))
                {
                    strWhere += " and  " + districtCodeWhere;
                }
            }
            string sql = @"select ROW_NUMBER() OVER ()index,*,case when yhcount=0 and zgcount=0 then '100.00%' else  concat(
        CASE
            WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00
            ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)
        END, '%') end zgl from hcqkcjtext('" + startTime + "','" + endTime + "','"+roletype+"') where 1 = 1 and xzq!= '东城区' and xzq!= '西城区'  " + strWhere + "  order by xzqdm,towndm asc ,villagedm asc limit " + limit + " offset (" + pageIndex + ") * " + limit + "";
            string sql_count = @"select count(1) cnt from hcqkcjtext('" + startTime + "','" + endTime + "','"+roletype+"') where 1 = 1 and xzq!= '东城区' and xzq!= '西城区'  " + strWhere + " ";

              if (cked == "true")
            {
                sql = @"select ROW_NUMBER() OVER ()index,*  from (select t1.xzq,t1.townold town,t1.villageold village,COALESCE(zcount, 0) zcount,COALESCE(hccount, 0) hccount,COALESCE(whccount, 0) whccount,COALESCE(hcl::text
, '0%') jcl,COALESCE(yhcount, 0) yhcount,COALESCE(zgcount, 0) zgcount,case when yhcount=0 and zgcount=0 then '100.00%' else  concat(
        CASE
            WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00
            ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)
        END, '%') end zgl,COALESCE(syyhcount, 0) syyhcount,t2.xzqdm,t2.towndm,t1.villagedm from zdcmd t1 left join  hcqkcjtext('" + startTime + "','" + endTime + "','"+roletype+"') t2 on t1.villagedm=t2.villagedm) t where 1 = 1 and xzq!= '东城区' and xzq!= '西城区'  " + strWhere + "  order by xzqdm,towndm asc ,villagedm asc limit " + limit + " offset (" + pageIndex + ") * " + limit + "";
                sql_count = @"select count(1) cnt from (select t1.xzq,t1.townold town,t1.villageold village,zcount,hccount,whccount,jcl,yhcount,zgcount,syyhcount,t2.xzqdm,t2.towndm,t1.villagedm from zdcmd t1 left join  hcqkcjtext('" + startTime + "','" + endTime + "') t2 on t1.villagedm=t2.villagedm) t where 1 = 1 and xzq!= '东城区' and xzq!= '西城区'  " + strWhere + " ";
            }


            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
           || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
           || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            var pageData = new Page<object>(list
                , pageIndex + 1, limit, count);
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex + 1, PageTotal = count, Total = count });

        }



        /// <summary>
        /// 村级工作进展核查明细
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("hc/evolvestatic/villagePostStaticDetail")]
        public RespRet<Page<object>> villagePostStaticDetail([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string category, [FromQuery] int pageIndex, [FromQuery] int limit, [FromQuery] string endTime, [FromQuery] string startTime, [FromQuery] string txtKeyWord, [FromQuery] string yhly)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            string where = "";
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                where += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }

            string strWhere = "  ";
            string strSqlWhere = " ";
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and town='" + towndm + "' and village='" + villagedm + "'";
                strSqlWhere+= " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "' and villagedm='" + villagedm + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and town='" + towndm + "'";
                strSqlWhere += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "' ";
            }
            if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' ";
                strSqlWhere += " and xzqdm='" + xzqdm + "'";
            }




            string sql = " ";

            if (category == "1")
            {
                strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' and yhly='"+ yhly + "' " + where;
                sql = @"   	SELECT   ROW_NUMBER() OVER ()index,xzq,towndm town,villagedm village,hzxm,address,fwbh  FROM(select t1.*,hc.fwzt fwzthc,jc.fwzt fwztjc from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num,
                        m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id  and m1.yhly='"+yhly+"') t1 join zm_buildings t3 on t1.fwbh=t3.fwbh  left join "+
                       " (select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4')  hc on hc.fwbh=t1.fwbh  left join (select fwbh,fwzt from  zm_pc_base_cj )  jc on jc.fwbh=t1.fwbh)" +
                       " zm_pc_yh_sqjz WHERE  row_num=1 and yhly=4 and date(create_on) between '" + startTime + "' and '"+ endTime + "' and fwzt is null and yhly="+yhly+" and fwzthc is null"+
                       " and fwztjc is  null    " + strWhere + " limit " + limit + " offset(" + pageIndex + ") * " + limit;
            }
            else if (category == "2")
            {
                sql = @" select ROW_NUMBER() OVER ()index, * from (select t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh, 
                t2.fwzt jcfwzt,t3.fwzt hcfwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh  left join  (select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4') t3 on t1.fwbh=t3.fwbh) m1  
                WHERE  (jcfwzt is null and hcfwzt is null)    and fwbh not in(
					                SELECT fwbh   FROM(select t1.*,hc.fwzt fwzthc,jc.fwzt fwztjc from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, 
                m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='"+yhly+"' ) t1 join zm_buildings t3 on t1.fwbh=t3.fwbh  left join (select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4')  "+
              "  hc on hc.fwbh=t1.fwbh  left join (select fwbh,fwzt from  zm_pc_base_cj )  jc on jc.fwbh=t1.fwbh) zm_pc_yh_sqjz WHERE  row_num=1 and yhly="+yhly+" and date(create_on)"+
               " between '"+startTime+"' and '"+endTime+"' and fwzt is null and yhly="+ yhly + " and fwzthc is null and fwztjc is  null  "+ strWhere + "   ) "+
               " "+ strWhere + where+ " limit " + limit + "  offset(" + pageIndex + ") * " + limit;

            }
            else if (category == "3")
            {
                strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' and jcly='"+yhly+"' " + where;
                sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on,t1.check_name,t1.jcly,t1.check_nickname create_name  from zm_pc_yhlog_dx_sqjz t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_sqjz t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null and fwbh not in(select fwbh from  zm_pc_base_sqjz where yhly='4' and fwzt is not null)) t2  where 1 = 1 and  t1.fwbh=t2.fwbh) t) t where 1=1 " + strWhere + "limit " + limit + " offset(" + pageIndex + ") * " + limit;
            }
            else if (category == "4")
            {
                strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' and jcly='"+yhly+"' " + where;
                sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on,t1.check_name,t1.jcly,t1.check_nickname create_name from zm_pc_yhlog_dx_sqjz t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_sqjz t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null and fwbh not in(select fwbh from  zm_pc_base_sqjz where yhly='4' and fwzt is not null)) t2  where 1 = 1 and  t1.fwbh=t2.fwbh) t where zg_value='1') t where 1=1 " + strWhere + "limit " + limit + " offset(" + pageIndex + ") * " + limit;
            }
            else if (category == "5")
            {
                strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' and jcly='"+yhly+"' " + where;
                sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on,t1.check_name,t1.jcly,t1.check_nickname create_name from zm_pc_yhlog_dx_sqjz t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_sqjz t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null  and fwbh not in(select fwbh from  zm_pc_base_sqjz where yhly='4' and fwzt is not null)) t2  where 1 = 1 and t1.fwbh=t2.fwbh and zg_value='0' ) t) t where  1=1 " + strWhere + "limit " + limit + " offset(" + pageIndex + ") * " + limit;
            }
            else if (category == "6") //已达标房屋明细
            {
                strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' and yhly='"+ yhly + "' " + where;
                sql = @"select ROW_NUMBER() OVER ()index,*, case when yhly='2' then '市级人员' when yhly='3' then '区级人员' when yhly='4' then '乡镇人员'  end yhlyname from (SELECT DISTINCT ON (fwbh) t1.*, t2.fwzt jcfwzt FROM (select m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='"+yhly+"') t1 "+
              " left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh  ORDER BY fwbh, create_on DESC) t WHERE (fwzt is null and jcfwzt is null) and jsrysstd='是' and fwbh not in(select fwbh from  zm_pc_base_sqjz where yhly='4' and fwzt is not null) " + strWhere + "limit " + limit + " offset(" + pageIndex + ") * " + limit;
            }
            else if (category == "7")//未达标房屋明细
            {
                strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' and yhly='"+ yhly + "' " + where;
                sql = @"select ROW_NUMBER() OVER ()index,*, case when yhly='2' then '市级人员' when yhly='3' then '区级人员' when yhly='4' then '乡镇人员'  end yhlyname from (SELECT DISTINCT ON (fwbh) t1.*, t2.fwzt jcfwzt FROM (select m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='"+yhly+"') t1 "+
              " left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh  ORDER BY fwbh, create_on DESC) t WHERE (fwzt is null and jcfwzt is null) and jsrysstd='否' and fwbh not in(select fwbh from  zm_pc_base_sqjz where yhly='4' and fwzt is not null) " + strWhere + "limit " + limit + " offset(" + pageIndex + ") * " + limit;
            }


            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            string sql_count = " ";

            if (category == "1")
            {

                sql = @"   	SELECT   count(1) cnt  FROM(select t1.*,hc.fwzt fwzthc,jc.fwzt fwztjc from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num,
                        m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id ) t1 join zm_buildings t3 on t1.fwbh=t3.fwbh  left join 
                        (select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4')  hc on hc.fwbh=t1.fwbh  left join (select fwbh,fwzt from  zm_pc_base_cj )  jc on jc.fwbh=t1.fwbh)" +
                             " zm_pc_yh_sqjz WHERE  row_num=1 and yhly=4 and date(create_on) between '" + startTime + "' and '" + endTime + "' and fwzt is null and yhly=" + yhly + " and fwzthc is null" +
                             " and fwztjc is  null    " + strWhere ;
            }
            else if (category == "2")
            {
                sql_count = @" select count(1) cnt  from (select t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh, 
                t2.fwzt jcfwzt,t3.fwzt hcfwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh  left join  (select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4') t3 on t1.fwbh=t3.fwbh) m1  
                WHERE  (jcfwzt is null and hcfwzt is null)    and fwbh not in(
					                SELECT fwbh   FROM(select t1.*,hc.fwzt fwzthc,jc.fwzt fwztjc from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, 
                m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='" + yhly + "' ) t1 join zm_buildings t3 on t1.fwbh=t3.fwbh  left join (select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4')  " +
             "  hc on hc.fwbh=t1.fwbh  left join (select fwbh,fwzt from  zm_pc_base_cj )  jc on jc.fwbh=t1.fwbh) zm_pc_yh_sqjz WHERE  row_num=1 and yhly=" + yhly + " and date(create_on)" +
              " between '" + startTime + "' and '" + endTime + "' and fwzt is null and yhly=" + yhly + " and fwzthc is null and fwztjc is  null  " + strWhere + "   ) " +
              " " + strWhere + where ;
            }
            else if (category == "3")
            {
                sql_count = @"select count(1) cnt  from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on,t1.jcly,t1.check_nickname create_name from zm_pc_yhlog_dx_sqjz t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_sqjz t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and  t1.fwbh=t2.fwbh) t) t where 1=1 " + strWhere + " ";
            }
            else if (category == "4")
            {
                sql_count = @"select count(1) cnt  from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on,t1.jcly,t1.check_nickname create_name from zm_pc_yhlog_dx_sqjz t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_sqjz t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and  t1.fwbh=t2.fwbh and zg_value='1') t where zg_value='1') t where 1=1  " + strWhere + "";
            }
            else if (category == "5")
            {
                sql_count = @"select count(1) cnt from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on,t1.check_name,t1.jcly from zm_pc_yhlog_dx_sqjz t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_sqjz t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and t1.fwbh=t2.fwbh and zg_value='0' ) t) t where  1=1  " + strWhere + "";
            }
            else if (category == "6") //已达标房屋明细
            {
                sql_count = @"select count(1) cnt from (SELECT DISTINCT ON (fwbh) t1.*, t2.fwzt jcfwzt FROM (select m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id) t1 
               left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh  ORDER BY fwbh, create_on DESC) t WHERE (fwzt is null and jcfwzt is null) and jsrysstd='是' " + strWhere ;
            }
            else if (category == "7")//未达标房屋明细
            {
                sql_count = @"select count(1) cnt from (SELECT DISTINCT ON (fwbh) t1.*, t2.fwzt jcfwzt FROM (select m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id) t1 
               left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh  ORDER BY fwbh, create_on DESC) t WHERE (fwzt is null and jcfwzt is null) and jsrysstd='否' " + strWhere ;
            }


            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }

        /// <summary>
        /// 获取当前房屋的核查记录中的详细信息
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("hc/{id}/restinfo/")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<HourseBaseInfo>), 200)]
        public RespRet<HourseBaseInfo> restinfo([FromRoute] string id)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<HourseBaseInfo>(false, "user not logined");
            }

            HourseBaseInfo model = new HourseBaseInfo();
            string sql = "  select *,case when (coalesce(yhnum,0) >0) then  CAST('21' AS INTEGER) else  CAST('20' AS INTEGER)  end AS pczt from " +
                "( select b1.*,yhnum, m1.unitname from (select t1.id,t1.fwbh,t1.yhly,t1.xzqdm,t1.town,t1.village,address,yhfwbh,hzxm,jzrks,zzrs,cs,fjsl,jzfjs,jzscxyaqcksl," +
                "jzxyssltsl,jzscmj,ckfhwggptzlsl,bdfwjzrydddzxcsl,mhqsl,fytsmz,xggdjsbp,jzsyjyplmhxts,jzszlwxdlgybjqs,case when wdwhjgfjgqsyyrkrgbdj =1 then '是' else '否' end wdwhjgfjgqsyyrkrgbdj,case when sstdsyyrkrclzx =1 then '是' else '否' end sstdsyyrkrclzx,case when gryssdcsszblyyqksddp =1 then '是' else '否' end gryssdcsszblyyqksddp," +
                "case when cjzwqtjyyt =0 then '否' else cjzwqtjyytmsg end cjzwqtjyytmsg,case when cs=1 and jzscxyaqcksl>=2 then '已达标'  else case when cs>=2 and jzscxyaqcksl>=2  and jzxyssltsl >=2 then '已达标' else sstdjsqk end  end sstdjsqk,jsrysstd,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj,cjzwqtjyyt,use_electric_bicycle,use_bottled_liquefied_gas,use_stacked_items," +
                "mhqwbcwhyx,jyplmhxtwbcwhyx,ygbjwfwhyx,use_electric_bicycle_photo,use_bottled_liquefied_gas_photo,use_stacked_items_photo,mhqwbcwhyx_photo," +
                "jyplmhxtwbcwhyx_photo,ygbjwfwhyx_photo,use_electric_bicycle_msg,use_bottled_liquefied_gas_msg,use_stacked_items_msg,mhqwbcwhyx_msg," +
                "jyplmhxtwbcwhyx_msg,ygbjwfwhyx_msg,use_electric_bicycle_zgqk,use_bottled_liquefied_gas_zgqk,use_stacked_items_zgqk,mhqwbcwhyx_zgqk," +
                "jyplmhxtwbcwhyx_zgqk,ygbjwfwhyx_msg_zgqk, use_electric_bicycle_zgphoto,use_bottled_liquefied_gas_zgphoto,use_stacked_items_zgphoto," +
                "mhqwbcwhyx_zgphoto,jyplmhxtwbcwhyx_zgphoto,ygbjwfwhyx_msg_zgphoto,slljdx,slljdx_photo, slljdx_msg,slljdx_zgqk,slljdx_zgphoto,zxqlddcsl," +
                "zxqlyhsyqsl,sign,notes,t1.create_by,t1.create_name,t1.create_on,t1.sfmzstd_photo,t1.current_location,t1.longitude,t1.latitude from zm_pc_baselog_sqjz t1, zm_pc_yhlog_sqjz t2 where t1.id = t2.base_id ) b1 left join zm_user_cj m1 on b1.create_by=m1.\"number\" left join (select fwbh,base_id,COALESCE(yhnum,0) yhnum, " +
                "COALESCE(zgnum,0) zgnum from  zm_pc_yhlog_sqjz t1 inner join  (select count(fwbh) yhnum,yhlog_id,sum(case when zg_value='1' then 1 else 0 end) zgnum " +
                "from zm_pc_yhlog_dx_sqjz  group by yhlog_id) t2 on t1.id=t2.yhlog_id) b2  on b1.id=b2.base_id) t  where id ='" + id + "'";

            var item = _dbContext.Database.GetDbConnection().Query<BuildingPcYhLogDetailDto>(sql).ToList();

            model.BuildingPcYhLogDetailDtoList = item;
            ///整改
            string sql_rectification = @"  select *from (select m1.*,check_id,check_name,zg_zp,zg_time,zg_by zg_name   from 
                    (select t1.base_id,t1.fwbh,t2.id dx_id from zm_pc_yhlog_sqjz t1 left join zm_pc_yhlog_dx_sqjz t2 on t1.id=t2.yhlog_id ) m1 ,
                    zm_pc_yhlog_dxzgson_sqjz m2  where m1.dx_id=m2.yhlog_dx_id ) t where base_id='" + id + "'";

            var itemLIst = _dbContext.Database.GetDbConnection().Query<RectificationYh>(sql_rectification).ToList();

            model.RectificationYhList = itemLIst;


            return new RespRet<HourseBaseInfo>(true, "Ok", model);
        }

        /// <summary>
        /// 导出村庄数据
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("hc/evolvestatic/ExportExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult ExportExcel([FromQuery] string startTime, [FromQuery] string endTime, [FromQuery]int yhly, [FromQuery] string cs, [FromQuery] string nickname)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                Xzq xzq = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                string strWhere = "  ";
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";
                }
                else if (user.RoleType == 3)
                {
                    strWhere += " and xzq='" + xzq.RegionName + "'";
                }
                else
                {
                    string xzqdm = xzq.RegionCode.Substring(0, 6);
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                string floor = "";
                if (!string.IsNullOrEmpty(cs) && cs != "0" && cs != "null")
                {
                    floor = " cs in (" + cs + ") ";
                }
                string toDay = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                    if (Convert.ToDateTime(endTime) > DateTime.Now)
                    {
                        table_fwzt = "zm_building_fwzt_" + DateTime.Now.Year;
                    }
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(toDay);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                //string sql = @"select ROW_NUMBER() OVER ()index,* from hcqkcjtext('" + startTime + "','" + endTime +" 23:59:59'," + yhly + ",'" + table_fwzt +"') where 1 = 1 and xzq!= '东城区' and xzq!= '西城区'  " + strWhere;
                string sql= "select ROW_NUMBER() OVER ()index ,*,case when yhcount=0 and zgcount=0 then '100.00%' else  concat( CASE " +
                "   WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00   ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)  END, '%') end zgl from " +
                " hcqkstatic('" + startTime + "'::timestamp, '" + endTime + " 23:59:59'::timestamp, " + yhly + ", '" + nickname + "', '" + floor + "','" + table_fwzt + "')  where 1=1 " + strWhere;
                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("房屋总数(栋)");
                row.CreateCell(3).SetCellValue("核查总数(栋)");
                row.CreateCell(4).SetCellValue("核查率");
                row.CreateCell(5).SetCellValue("已达标房屋数量");
                row.CreateCell(6).SetCellValue("未达标房屋数量");
                row.CreateCell(7).SetCellValue("达标率");
                row.CreateCell(8).SetCellValue("隐患数量（个）");
                row.CreateCell(9).SetCellValue("整改数量（个）");
                row.CreateCell(10).SetCellValue("剩余隐患数量（个）");

                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value+"";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "zcount")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "hccount")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "hcl")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "ydbcount")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "wdbcount")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "dbl")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                            else if (kvp.Key == "yhcount")
                            {
                                row.CreateCell(8).SetCellValue(val);
                            }
                            else if (kvp.Key == "zgcount")
                            {
                                row.CreateCell(9).SetCellValue(val);
                            }
                            else if (kvp.Key == "syyhcount")
                            {
                                row.CreateCell(10).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村民经营性自建房消防安全排查治理核查工作进展统计.xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }


        /// <summary>
        /// 导出核查房屋明细数据
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("hc/evolvestatic/ExportDetailedExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult ExportDetailedExcel([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string roletype)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                string strWhere = "  ";

                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";

                }
                else if (user.RoleType == 3)  //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 4)
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
                else if (user.RoleType == 5)
                {
                    strWhere += " and villagedm='" + model.RegionCode + "'";
                }

                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and town='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and village='" + villagedm + "'";
                }
                if (string.IsNullOrEmpty(roletype))
                {
                    roletype= " 4";
                }



                string sql = @"	select * from (select  ROW_NUMBER() OVER ()index ,*,case when  create_on  is null then '未核验' else '已核验' end jczt
             from 
						(select n1.fwbh,case when m2.id is null then n1.id else m2.id end id,m2.yhly,case when  m2.xzqdm is null  then n1.xzqdm else  m2.xzqdm end  xzqdm,
						case when m2.town is null  then n1.towndm else  m2.town end  town,case when  m2.village  is null  then n1.villagedm else  m2.village end village ,
						case when  m2.address is null then n1.address else m2.address end address,case when  m2.hzxm is null then n1.hzxm else m2.hzxm end hzxm,
						case when m2.jzrks  is null then n1.jzrks else m2.jzrks end jzrks,
						case when m2.zzrs  is null then n1.zzrs else m2.zzrs end zzrs,
						case when m2.cs is null then n1.cs else m2.cs end cs,
						case when m2.fjsl  is null then n1.fjsl else m2.fjsl end fjsl,
						case when m2.jzfjs  is null then n1.jzfjs else m2.jzfjs end jzfjs,
						case when m2.jzscxyaqcksl  is null then n1.jzscxyaqcksl else m2.jzscxyaqcksl end jzscxyaqcksl,
						case when m2.jzxyssltsl  is null then n1.jzxyssltsl else m2.jzxyssltsl end jzxyssltsl,
						case when m2.jzscmj  is null then n1.jzscmj else m2.jzscmj end jzscmj,
						case when m2.ckfhwggptzlsl  is null then n1.ckfhwggptzlsl else m2.ckfhwggptzlsl end ckfhwggptzlsl,
						case when m2.bdfwjzrydddzxcsl  is null then n1.bdfwjzrydddzxcsl else m2.bdfwjzrydddzxcsl end bdfwjzrydddzxcsl,
						case when m2.mhqsl  is null then n1.mhqsl else m2.mhqsl end mhqsl,
						case when m2.fytsmz  is null then n1.fytsmz else m2.fytsmz end fytsmz,
						case when m2.xggdjsbp  is null then n1.xggdjsbp else m2.xggdjsbp end xggdjsbp,
						case when m2.jzsyjyplmhxts  is null then n1.jzsyjyplmhxts else m2.jzsyjyplmhxts end jzsyjyplmhxts,
						case when m2.jzszlwxdlgybjqs  is null then n1.jzszlwxdlgybjqs else m2.jzszlwxdlgybjqs end jzszlwxdlgybjqs,
						case when m2.wdwhjgfjgqsyyrkrgbdj  is null then n1.wdwhjgfjgqsyyrkrgbdj else m2.wdwhjgfjgqsyyrkrgbdj end wdwhjgfjgqsyyrkrgbdj,
						case when m2.sstdsyyrkrclzx  is null then n1.sstdsyyrkrclzx else m2.sstdsyyrkrclzx end sstdsyyrkrclzx,
                        case when m2.gryssdcsszblyyqksddp  is null then n1.gryssdcsszblyyqksddp else m2.gryssdcsszblyyqksddp end gryssdcsszblyyqksddp,
                        case when m2.cjzwqtjyytmsg  is null then n1.cjzwqtjyytmsg else m2.cjzwqtjyytmsg end cjzwqtjyytmsg,
                        m2.jsrysstd ,
                        case when m2.sstdjsqk  is null then n1.sstdjsqk else m2.sstdjsqk end sstdjsqk,
                        m2.create_on ,m2.create_by,m2.create_ip,m2.pcsj,m2.cjzwqtjyyt,case when m2.xzq is null then n1.xzq else  m2.xzq  end xzq,
                        case when m2.towndm is null then n1.towndm else m2.towndm end towndm,
                        case when m2.villagedm is null then n1.village else m2.villagedm end villagedm ,m2.cdfs,m2.cdfsqtbz,m2.czryrs,m2.sfazfztsss,m2.ywgdlttxwd,m2.tdsfdb,
                        use_electric_bicycle,use_bottled_liquefied_gas,use_stacked_items,mhqwbcwhyx,jyplmhxtwbcwhyx,ygbjwfwhyx,slljdx,sfczckfhwggptzl,zg_value,check_id,nickname
                         from (select * from (select t1.*,fwzt from (select p2.* from zm_buildings p1, zm_pc_base p2 where p1.fwbh=p2.fwbh) t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) m1  where fwzt is null    and xzq!='东城区' and xzq!='西城区' and fwbh not in(select fwbh from zm_pc_base_sqjz where yhly='4' and fwzt is not null)  and village not in ('110105022004','110105022046','110105022201','110105022045') )n1  left join  (select DISTINCT ON (fwbh) t1.*,nickname from (select t1.*,use_electric_bicycle,use_bottled_liquefied_gas,use_stacked_items,mhqwbcwhyx,jyplmhxtwbcwhyx,ygbjwfwhyx,slljdx,sfczckfhwggptzl,zg_value ,check_id from zm_pc_baselog_sqjz t1,(select base_id, 
                        case when check_id='use_electric_bicycle' and zg_value=0 then '有隐患未整改'  when check_id='use_electric_bicycle' and zg_value=1 then '有隐患已整改' else '否'   end use_electric_bicycle,
                        case when check_id='use_bottled_liquefied_gas' and zg_value=0 then '有隐患未整改'  when check_id='use_bottled_liquefied_gas' and zg_value=1 then '有隐患已整改' else '否' end use_bottled_liquefied_gas,
                        case when check_id='use_stacked_items' and zg_value=0 then '有隐患未整改'  when check_id='use_stacked_items' and zg_value=1 then '有隐患已整改' else '否' end use_stacked_items,
                        case when check_id='mhqwbcwhyx' and zg_value=0 then '有隐患未整改'  when check_id='mhqwbcwhyx' and zg_value=1 then '有隐患已整改' else '否' end mhqwbcwhyx,
                        case when check_id='jyplmhxtwbcwhyx' and zg_value=0 then '有隐患未整改'  when check_id='jyplmhxtwbcwhyx' and zg_value=1 then '有隐患已整改' else '否'  end jyplmhxtwbcwhyx,
                        case when check_id='ygbjwfwhyx' and zg_value=0 then '有隐患未整改'  when check_id='ygbjwfwhyx' and zg_value=1 then '有隐患已整改' else '否'  end ygbjwfwhyx,
                        case when check_id='slljdx' and zg_value=0 then '有隐患未整改'  when check_id='slljdx' and zg_value=1 then '有隐患已整改' else '否'  end slljdx,
                        case when check_id='sfczckfhwggptzl' and zg_value=0 then '有隐患未整改'  when check_id='sfczckfhwggptzl' and zg_value=1 then '有隐患已整改' else '否'  end sfczckfhwggptzl,"+
                        "zg_value,check_id from zm_pc_yhlog_sqjz m1 left join zm_pc_yhlog_dx_sqjz m2 on m1.id=m2.yhlog_id) t2 where t1.id=t2.base_id ) t1 left join zm_user_cj t2 on t1.create_by=t2.\"number\""+  
                    " where 1=1 and date(t1.create_on)  between '"+startTime+"' and '"+endTime+"' and yhly='"+roletype+"' ORDER BY fwbh, t1.create_on DESC ) m2 on n1.fwbh=m2.fwbh  order by village) t where 1=1  ) t where 1=1 and yhly='"+ roletype + "' "+strWhere;


                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
               // _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("房主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("居住人口数");
                row.CreateCell(8).SetCellValue("租住人口数");
                row.CreateCell(9).SetCellValue("房间数量");
                row.CreateCell(10).SetCellValue("出租房间数量");
                row.CreateCell(11).SetCellValue("层数");
                row.CreateCell(12).SetCellValue("疏散楼梯数量");
                row.CreateCell(13).SetCellValue("安全出口数量");
                row.CreateCell(14).SetCellValue("防护网数量");
                row.CreateCell(15).SetCellValue("电动自行车数量");
                row.CreateCell(16).SetCellValue("灭火器数量");
                row.CreateCell(17).SetCellValue("防烟逃生面罩");
                row.CreateCell(18).SetCellValue("悬挂固定警示标牌");
                row.CreateCell(19).SetCellValue("简易喷淋灭火系统数");
                row.CreateCell(20).SetCellValue("联网型独立感烟报警器数");
                row.CreateCell(21).SetCellValue("电动自行车、电动平衡车及其蓄电池在房屋公共区域、疏散走道、楼梯间、安全出口或房间内停放、充电");
                row.CreateCell(22).SetCellValue("出租房屋的公共区域、租住房间内、地下室、半地下室使用瓶装液化石油气");
                row.CreateCell(23).SetCellValue("疏散走道、楼梯间、安全出口处堆放物品影响人员疏散");
                row.CreateCell(24).SetCellValue("灭火器未保存完好有效");
                row.CreateCell(25).SetCellValue("简易喷淋灭火系统未保存完好有效");
                row.CreateCell(26).SetCellValue("烟感报警未否完好有效");
                row.CreateCell(27).SetCellValue("私拉乱接电线");
                row.CreateCell(28).SetCellValue("是否存在窗口、防护网、广告牌、铁栅栏");
                row.CreateCell(29).SetCellValue("核查人");
                row.CreateCell(30).SetCellValue("核查时间");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value + "";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "towndm")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "villagedm")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzrks")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zzrs")
                            {
                                row.CreateCell(8).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fjsl")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzfjs")
                            {
                                row.CreateCell(10).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "cs")
                            {
                                row.CreateCell(11).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzxyssltsl")
                            {
                                row.CreateCell(12).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzscxyaqcksl")
                            {
                                row.CreateCell(13).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "ckfhwggptzlsl")
                            {
                                row.CreateCell(14).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "bdfwjzrydddzxcsl")
                            {
                                row.CreateCell(15).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "mhqsl")
                            {
                                row.CreateCell(16).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fytsmz")
                            {
                                row.CreateCell(17).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "xggdjsbp")
                            {
                                row.CreateCell(18).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzsyjyplmhxts")
                            {
                                row.CreateCell(19).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzszlwxdlgybjqs")
                            {
                                row.CreateCell(20).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_electric_bicycle")
                            {
                                row.CreateCell(21).SetCellValue(val);
                            }
                            else if (kvp.Key == "use_bottled_liquefied_gas")
                            {
                                row.CreateCell(22).SetCellValue(val);
                            }
                            else if (kvp.Key == "use_stacked_items")
                            {
                                row.CreateCell(23).SetCellValue(val);
                            }
                            else if (kvp.Key == "mhqwbcwhyx")
                            {
                                row.CreateCell(24).SetCellValue(val);
                            }
                            else if (kvp.Key == "jyplmhxtwbcwhyx")
                            {
                                row.CreateCell(25).SetCellValue(val);
                            }
                            else if (kvp.Key == "ygbjwfwhyx")
                            {
                                row.CreateCell(26).SetCellValue(val);
                            }
                            else if (kvp.Key == "slljdx")
                            {
                                row.CreateCell(27).SetCellValue(val);
                            }
                            else if (kvp.Key == "sfczckfhwggptzl")
                            {
                                row.CreateCell(28).SetCellValue(val);
                            }
                            else if (kvp.Key == "nickname")
                            {
                                row.CreateCell(29).SetCellValue(val);
                            }
                            else if (kvp.Key == "create_on")
                            {
                                row.CreateCell(30).SetCellValue(val);
                            }

                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                string rolename = "";
                if (roletype == "4") 
                {
                    rolename = "乡镇核验";
                }else if (roletype == "3")
                {
                    rolename = "区级核验";
                }else if (roletype == "2")
                {
                    rolename = "市级核验";
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村民经营性自建房消防安全排查治理村級工作进展统计（"+ rolename +"）"+DateTime.Now.ToString("yyyyMMddHHmmss")+".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 某栋房屋的核查、核查记录
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("hc/baseInfo/baseInfoDetaile")]
        public RespRet<Page<object>> baseInfoDetaile([FromQuery] string fwbh, [FromQuery] string jcly, [FromQuery] int pageIndex, [FromQuery] int limit)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            if (string.IsNullOrEmpty(jcly))
            {
                jcly = "4";
            }

            string strWhere = "";
            string sql = @"select  ROW_NUMBER() OVER ()index ,*   from ( select b1.*,COALESCE(yhnum,0) yhnum,case when yhnum is null then '已核查无隐患' else  '已核查有隐患' end yhzt,COALESCE(zgnum,0) zgnum,case when fwzt is null then '正常出租' else case when fwzt=1 then '不出租' else  '已拆除' end end fwztname,
                         case when zgnum is null then '/' else case when zgnum=0 then '未整改' else  '已整改' end end zgzt,'详情' cz  from (select t2.hzxm,t2.address,t1.fwbh,t1.jsrysstd,t1.id,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj ,yhly,create_name  ,fwzt
                           from zm_pc_baselog_sqjz t1,zm_buildings t2 where t1.fwbh=t2.fwbh and t1.yhly='" + jcly + "' ) b1 left join "+
                          " (select count(fwbh) yhnum, yhlog_id, sum(case when zg_value= '1' then 1 else 0 end) zgnum from zm_pc_yhlog_dx_sqjz  " +
                          " group by yhlog_id) b2 on b1.id = b2.yhlog_id  order by pcsj desc)  t    " +
                      " where fwbh='" + fwbh + "' " + strWhere + "   limit " + limit + " offset " + limit * pageIndex;


            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
         || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
         || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select  count(1) cnt  from ( select b1.*,COALESCE(yhnum,0) yhnum,case when yhnum is null then '已核查无隐患' else  '已核查有隐患' end yhzt,COALESCE(zgnum,0) zgnum, case when fwzt is null then '正常出租' else case when fwzt=1 then '不出租' else  '已拆除' end end fwztname,
                         case when zgnum is null then '/' else case when zgnum=0 then '未整改' else  '已整改' end end zgzt,'详情' cz  from (select t2.hzxm,t2.address,t1.fwbh,t1.jsrysstd,t1.id,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj  ,yhly,create_name,fwzt
                           from zm_pc_baselog_sqjz t1,zm_buildings t2 where t1.fwbh=t2.fwbh and t1.yhly='" + jcly + "') b1 left join " +
                         "  (select count(fwbh) yhnum, yhlog_id, sum(case when zg_value= '1' then 1 else 0 end) zgnum from zm_pc_yhlog_dx_sqjz " +
                         "  group by yhlog_id) b2 on b1.id = b2.yhlog_id order by pcsj desc)  t    where fwbh='" + fwbh + "'" + strWhere;

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }


        /// <summary>
        /// 分类导出明细
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("hc/evolvestatic/ExportHouseDetailedExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult ExportHouseDetailedExcel([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string category, [FromQuery] string yhly)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                string strWhere = " ";
                string strSqlWhere = "";
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";
                }
                else if (user.RoleType == 3)  //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 4)
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
                else if (user.RoleType == 5)
                {
                    strWhere += " and villagedm='" + model.RegionCode + "'";
                }

                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                    strSqlWhere += " and town='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "'"; strSqlWhere += " and village='" + villagedm + "'";
                }

                string sql = "";
                string filename = "";
                string table_fwzt = "";
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                if (category == "1")
                {
                    filename = startTime + "-" + endTime + "已核查明细";
                    strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' " ;
                    sql = @"  	SELECT   ROW_NUMBER() OVER ()index,xzq,towndm town,villagedm village,hzxm,address,fwbh  FROM(select t1.*,hc.fwzt fwzthc,jc.fwzt fwztjc from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num,
                        m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='"+yhly+"' ) t1 join zm_buildings t3 on t1.fwbh=t3.fwbh  left join "+
                        "(select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4')  hc on hc.fwbh=t1.fwbh  left join (select fwbh,fwzt from  zm_pc_base_cj )  jc on jc.fwbh=t1.fwbh)" +
                       " zm_pc_yh_sqjz WHERE  row_num=1 and yhly=4 and date(create_on) between '" + startTime + "' and '"+ endTime + "' and fwzt is null and yhly="+yhly+" and fwzthc is null"+
                       " and fwztjc is  null       " + strWhere;
                }
                else if (category == "2")
                {
                    filename = startTime + "-" + endTime + "未核查明细";
                    //sql = @" select  ROW_NUMBER() OVER ()index,*  from (select t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh, 
                    //  t2.fwzt jcfwzt,t3.fwzt hcfwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh  left join  (select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4') t3 on t1.fwbh=t3.fwbh) m1  
                    //  WHERE  (jcfwzt is null and hcfwzt is null)    and fwbh not in(
                    //                   SELECT fwbh   FROM(select t1.*,hc.fwzt fwzthc,jc.fwzt fwztjc from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, 
                    //  m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='"+yhly+"' ) t1 join zm_buildings t3 on t1.fwbh=t3.fwbh  left join (select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4')  " +
                    //"  hc on hc.fwbh=t1.fwbh  left join (select fwbh,fwzt from  zm_pc_base_cj )  jc on jc.fwbh=t1.fwbh) zm_pc_yh_sqjz WHERE  row_num=1 and yhly=" + yhly + " and date(create_on)" +
                    //" between '" + startTime + "' and '" + endTime + "' and fwzt is null and yhly=" + yhly + " and fwzthc is null and fwztjc is  null  " + strSqlWhere + "   ) " +
                    //" " + strSqlWhere;
                    sql = "select  ROW_NUMBER() OVER ()index,*  from (select xzq,xzqdm,town towndm,towndm town,villagedm village,village villagedm,hzxm,address,fwbh from " +
                      "  zm_buildings where  fwbh not in(SELECT DISTINCT fwbh FROM "+table_fwzt+" WHERE final_fwzt IS NOT NULL AND final_create_on = '"+endTime+" 23:59:59)   and fwbh not in(  SELECT fwbh   FROM(select t1.* from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
                      "      m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='"+yhly+ "' and date(m1.create_on) between '"+ startTime + "' and '"+ endTime + "' ) t1  WHERE  row_num=1 and yhly=4 and date(create_on) " +
                     "   between '"+startTime+"' and '"+endTime+ "'  and fwbh not in (SELECT DISTINCT fwbh FROM "+table_fwzt+" WHERE final_fwzt IS NOT NULL AND final_create_on = '"+endTime+" 23:59:59) " +
                     "  "+strSqlWhere+"  )  t  order by village)   "+ strWhere + "  order by village  ) t ";

                }
                else if (category == "3")
                {
                    filename = startTime + "-" + endTime + "发现隐患情况";
                    strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' and jcly='"+ yhly + "' "  ;
                    sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on,t1.check_name,t1.jcly,t1.check_nickname create_name from zm_pc_yhlog_dx_sqjz t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_sqjz t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and  t1.fwbh=t2.fwbh) t) t where 1=1 " + strWhere;
                }
                else if (category == "4")
                {
                    filename = startTime + "-" + endTime + "已整改隐患情况";
                    strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' and jcly='" + yhly + "' ";
                    sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on,t1.check_name,t1.jcly,t1.check_nickname create_name from zm_pc_yhlog_dx_sqjz t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_sqjz t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and  t1.fwbh=t2.fwbh) t where zg_value='1') t where 1=1  " + strWhere ;
                }
                else if (category == "5")
                {
                    filename = startTime + "-" + endTime + "未整改隐患情况";
                    strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' and jcly='" + yhly + "' ";
                    sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on,t1.check_name,t1.jcly,t1.check_nickname create_name from zm_pc_yhlog_dx_sqjz t1,(select * from  (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_sqjz t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and t1.fwbh=t2.fwbh and zg_value='0' ) t) t where 1=1 " + strWhere ;
                }
                else if (category == "6") //已达标房屋明细
                {
                    filename = startTime + "-" + endTime + "核查已达标房屋情况";
                    strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' " ;
                    sql = @"select ROW_NUMBER() OVER ()index,*, case when yhly='2' then '市级人员' when yhly='3' then '区级人员' when yhly='4' then '乡镇人员'  end yhlyname from (SELECT DISTINCT ON (fwbh) t1.*, t2.fwzt jcfwzt FROM (select m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='"+yhly+"') t1 "+
              " left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh  ORDER BY fwbh, create_on DESC) t WHERE (fwzt is null and jcfwzt is null) and jsrysstd='是' " + strWhere;
                }
                else if (category == "7")//未达标房屋明细
                {
                    filename = startTime + "-" + endTime + "核查未达标房屋情况";
                    strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' " ;
                    sql = @"select ROW_NUMBER() OVER ()index,*, case when yhly='2' then '市级人员' when yhly='3' then '区级人员' when yhly='4' then '乡镇人员'  end yhlyname from (SELECT DISTINCT ON (fwbh) t1.*, t2.fwzt jcfwzt FROM (select m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='"+yhly+"') t1 "+
               " left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh  ORDER BY fwbh, create_on DESC) t WHERE (fwzt is null and jcfwzt is null) and jsrysstd='否' " + strWhere ;
                }


                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题

                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇/街名称");
                row.CreateCell(3).SetCellValue("村/社区名称");
                row.CreateCell(4).SetCellValue("房主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("核查人员姓名");
                row.CreateCell(8).SetCellValue("核查时间");
                if (category == "3" || category == "4" || category == "5")
                {
                    row.CreateCell(9).SetCellValue("隐患项");
                }
                
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value + "";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "villagedm")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "towndm")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "create_name")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                            else if (kvp.Key == "create_on")
                            {
                                row.CreateCell(8).SetCellValue(val);
                            }
                            if (category == "3" || category == "4" || category == "5")
                            {
                                if (kvp.Key == "check_name")
                                {
                                    row.CreateCell(9).SetCellValue(val);
                                }
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村民经营性自建房消防安全排查治理" + filename + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }



        /// <summary>
        /// 核验统计
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("hchourse/HcHourseList")]
        public RespRet<Page<object>> HcHourseList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string filed, [FromQuery] int pageIndex, [FromQuery] int pagesize, [FromQuery] string endTime, [FromQuery] string startTime, [FromQuery] string txtKeyWord, [FromQuery] string roletype)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            string where = "";
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                if (filed == "total")
                {
                    where += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%' )";
                }
                else
                {
                    where += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%'  or nickname like '%" + txtKeyWord + "%')";
                }
            }

            string strWhere = "  ";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and town='" + towndm + "' and village='" + villagedm + "'";
                sqlWhere += " and m1.xzqdm='" + xzqdm + "' and m1.town='" + towndm + "' and m1.village='" + villagedm + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and town='" + towndm + "'";
                sqlWhere += " and m1.xzqdm='" + xzqdm + "' and m1.town='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' ";
                sqlWhere += " and m1.xzqdm='" + xzqdm + "' ";
            }

            //if (!string.IsNullOrEmpty(cs))
            //{
            //    if (cs.Contains("3"))
            //    {
            //        string[] arrr = cs.Split(',');
            //        if (arrr.Length == 2)
            //        {
            //            string cssql = "";
            //            for (int k = 0; k < arrr.Length; k++)
            //            {
            //                if (arrr[k] == "3")
            //                {
            //                    cssql += "  cs >=3 OR ";
            //                }
            //                if (arrr[k] == "1")
            //                {
            //                    cssql += "  cs=1 OR ";
            //                }
            //                if (arrr[k] == "2")
            //                {
            //                    cssql += " cs =2 OR ";
            //                }
            //            }

            //            where += " and (" + (cssql.Substring(0, cssql.Length - 3)) + ")";
            //        }
            //        else
            //        {
            //            where += " and cs >=" + cs;
            //        }
            //    }
            //    else
            //    {
            //        where += " and cs in(" + cs + ")";
            //    }
            //}
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = " ";

            if (filed == "total")
            {
                sql = "select  ROW_NUMBER() OVER ()index ,*from (select t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh " +
                   " from zm_buildings t1 where \"CreateOn\" <='" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) )   " + where+ " order by village) t where  1=1  " + where + "  limit " + pagesize + " offset " + pagesize * (pageIndex - 1);
            }
            else if (filed == "hccount")
            {
                sql = @" select ROW_NUMBER() OVER ()index ,*   from(SELECT t.*,nickname  FROM(select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,hzxm,address,fwbh,cs,create_on,create_name,create_by, case when yhly='3' then '区级核验' when yhly='2' then '市级核验'   else '乡镇核验' end yhly from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
                  "  m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='" + roletype + "' and (m1.create_on) " +
                  "  between '" + startTime + "' and '" + endTime + " 23:59:59'  " + sqlWhere + ") t1  WHERE  row_num=1 and yhly=" + roletype + " and (create_on) " +
                  "  between '" + startTime + "' and '" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in(select DISTINCT fwbh from "+table_fwzt+" where  (final_fwzt IS NOT NULL) and (final_create_on = '"+ endTime + " 23:59:59' )) ) " +
                  " t   left join zm_user_cj m  on t.create_by=m.\"number\"  order by village desc,t.create_on desc) t where 1=1  " + where + "  limit " + pagesize + " offset " + pagesize * (pageIndex-1);
             //   sql = "select ROW_NUMBER() OVER ()index ,* from (select * from temp1 order by create_name desc,village desc, create_on desc  limit 200 offset 0 )  t ";

            }
            else if (filed == "whccount")
            {
                sql = "select  ROW_NUMBER() OVER ()index ,*from (select t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh "+
                   " from zm_buildings t1 where \"CreateOn\" <='"+endTime+" 23:59:59' "+strWhere+ "  and fwbh not in( select DISTINCT fwbh from "+table_fwzt+" where  (final_fwzt IS NOT NULL) and (final_create_on = '"+ endTime + " 23:59:59' ))  " +
                   "  and fwbh not in(" +
                   " select fwbh from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num,   m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='" + roletype + "'"+
                   " and (m1.create_on)   between '"+startTime+"' and '"+endTime+" 23:59:59'   "+sqlWhere+" ) t1  WHERE  row_num=1 and yhly=" + roletype + " and (create_on)   between '"+startTime+"' and '"+endTime+" 23:59:59' "+
                   " "+ strWhere + "  and fwbh not in(	 select DISTINCT fwbh from "+table_fwzt+" where  (final_fwzt IS NOT NULL) and (final_create_on = '"+ endTime + " 23:59:59' )) )) t where 1=1  " + where + "  limit " + pagesize + " offset " + pagesize * (pageIndex - 1);

            }
            else if (filed == "ydbcount")
            {
                sql = @" select ROW_NUMBER() OVER ()index ,*   from(SELECT t.*,nickname  FROM(select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,hzxm,address,fwbh,cs,create_on,create_name,create_by,jsrysstd, case when yhly='3' then '区级核验' when yhly='2' then '市级核验'   else '乡镇核验' end yhly from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
                  "  m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='" + roletype + "' and (m1.create_on) " +
                  "  between '" + startTime + "' and '" + endTime + " 23:59:59'  " + sqlWhere + ") t1  WHERE  row_num=1 and yhly=" + roletype + " and (create_on) " +
                  "  between '" + startTime + "' and '" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) ) " +
                  " t   left join zm_user_cj m  on t.create_by=m.\"number\"  order by village desc,t.create_on desc) t where 1=1 and jsrysstd='是' " + where + "  limit " + pagesize + " offset " + pagesize * (pageIndex - 1);
            }
            else if (filed == "wdbcount")
            {
                sql = @" select ROW_NUMBER() OVER ()index ,*   from(SELECT t.*,nickname  FROM(select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,hzxm,address,fwbh,cs,create_on,create_name,create_by,jsrysstd, case when yhly='3' then '区级核验' when yhly='2' then '市级核验'   else '乡镇核验' end yhly from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
                  "  m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='" + roletype + "' and (m1.create_on) " +
                  "  between '" + startTime + "' and '" + endTime + " 23:59:59'  " + sqlWhere + ") t1  WHERE  row_num=1 and yhly=" + roletype + " and (create_on) " +
                  "  between '" + startTime + "' and '" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) ) " +
                  " t   left join zm_user_cj m  on t.create_by=m.\"number\"  order by village desc,t.create_on desc) t where 1=1 and (jsrysstd ='否' OR jsrysstd is null ) " + where + "  limit " + pagesize + " offset " + pagesize * (pageIndex - 1);

            }
            else if (filed == "yhcount")
            {
                sql = "select ROW_NUMBER() OVER ()index ,*  from (SELECT  m1.xzqdm,m1.xzq,m1.town towndm,m1.towndm town,m1.village villagedm,m1.villagedm village,hzxm,address,m1.fwbh ,cs ,m1.create_on," +
                    "check_id,check_name,check_sm,check_by,t3.nickname,case when zg_value=1 then '已整改' else '未整改' end zg_value,case when jczt='2' then '已复合通过' when jczt='1' then '待复核' else '未复核'" +
                    " end jczt,case when jcly='3' then '区级核验' when jcly='2' then '市级核验'   else '乡镇核验' end yhly   FROM zm_pc_yhlog_dx_sqjz m1 left join zm_pc_base t2 on m1.fwbh=t2.fwbh left join zm_user_cj t3 on m1.check_by=t3.\"number\"  WHERE m1.create_on between '" + startTime+"' " +
                    "and '"+endTime+" 23:59:59'    "+sqlWhere+"  AND jcly = "+roletype+ " and m1.fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) order by villagedm, create_on desc) t where 1=1  " + where + "  limit " + pagesize + " offset " + pagesize * (pageIndex - 1);
            }
            else if (filed == "zgcount")
            {
                sql = "select ROW_NUMBER() OVER ()index ,*  from (SELECT  m1.xzqdm,m1.xzq,m1.town towndm,m1.towndm town,m1.village villagedm,m1.villagedm village,hzxm,address,m1.fwbh ,cs ,m1.create_on," +
                    "check_id,check_name,check_sm,check_by,t3.nickname,case when zg_value=1 then '已整改' else '未整改' end zg_value,case when jczt='2' then '已复合通过' when jczt='1' then '待复核' else '未复核'" +
                    " end jczt,case when jcly='3' then '区级核验' when jcly='2' then '市级核验'   else '乡镇核验' end yhly   FROM zm_pc_yhlog_dx_sqjz m1 left join zm_pc_base t2 on m1.fwbh=t2.fwbh left join zm_user_cj t3 on m1.check_by=t3.\"number\"  WHERE m1.create_on between '" + startTime + "' " +
                    "and '" + endTime + " 23:59:59'    " + sqlWhere + "  AND jcly = " + roletype + " and m1.fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) order by villagedm, create_on desc) t where 1=1  and zg_value='已整改'  " + where + "  limit " + pagesize + " offset " + pagesize * (pageIndex - 1);
            }
            else if (filed == "sycount")
            {
                sql = "select ROW_NUMBER() OVER ()index ,*  from (SELECT  m1.xzqdm,m1.xzq,m1.town towndm,m1.towndm town,m1.village villagedm,m1.villagedm village,hzxm,address,m1.fwbh ,cs ,m1.create_on," +
                    "check_id,check_name,check_sm,check_by,t3.nickname,case when zg_value=1 then '已整改' else '未整改' end zg_value,case when jczt='2' then '已复合通过' when jczt='1' then '待复核' else '未复核'" +
                    " end jczt,case when jcly='3' then '区级核验' when jcly='2' then '市级核验'   else '乡镇核验' end yhly   FROM zm_pc_yhlog_dx_sqjz m1 left join zm_pc_base t2 on m1.fwbh=t2.fwbh left join zm_user_cj t3 on m1.check_by=t3.\"number\"  WHERE m1.create_on between '" + startTime + "' " +
                    "and '" + endTime + " 23:59:59'    " + sqlWhere + "  AND jcly = " + roletype + " and m1.fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) order by villagedm, create_on desc) t where 1=1  and zg_value='未整改'  " + where + "  limit " + pagesize + " offset " + pagesize * (pageIndex - 1);
            }
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            string sql_count = " ";

            if (filed == "total")
            {

                sql_count = "select   count(1) cnt from (select t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh " +
                  " from zm_buildings t1 where \"CreateOn\" <='" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in(	select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) )   " + where + " order by village) t where  1=1  " + where;
            }
            else if (filed == "hccount")
            {
                sql_count = @" select  count(1) cnt   from(SELECT t.*,nickname  FROM(select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,hzxm,address,fwbh,cs,create_on,create_name,create_by, case when yhly='3' then '区级核验' when yhly='2' then '市级核验'   else '乡镇核验' end yhly from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
              "  m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='" + roletype + "' and (m1.create_on) " +
              "  between '" + startTime + "' and '" + endTime + " 23:59:59'  " + sqlWhere + ") t1  WHERE  row_num=1 and yhly=" + roletype + " and (create_on) " +
              "  between '" + startTime + "' and '" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in(	select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) ) " +
              " t   left join zm_user_cj m  on t.create_by=m.\"number\"   order by village desc,t.create_on desc) t where 1=1 " + where;
                //   sql_count = "select count(1) cnt from temp1"; 
            }
            else if (filed == "whccount")
            {
                sql_count = "select  count(1) cnt from (select t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh " +
                  " from zm_buildings t1 where \"CreateOn\" <='" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) )   and fwbh not in(" +
                  " select fwbh from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num,   m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='" + roletype + "'" +
                  " and (m1.create_on)   between '" + startTime + "' and '" + endTime + " 23:59:59'   " + sqlWhere + " ) t1  WHERE  row_num=1 and yhly=" + roletype + " and (create_on)   between '" + startTime + "' and '" + endTime + " 23:59:59' " +
                  " " + strWhere + "  and fwbh not in(	select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' )  )) order by village desc) t where 1=1  " + where;

            }
            else if (filed == "ydbcount")
            {
                sql_count = @" select count(1) cnt   from(SELECT t.*,nickname  FROM(select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,hzxm,address,fwbh,cs,create_on,create_name,create_by,jsrysstd, case when yhly='3' then '区级核验' when yhly='2' then '市级核验'   else '乡镇核验' end yhly from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
                  "  m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='" + roletype + "' and (m1.create_on) " +
                  "  between '" + startTime + "' and '" + endTime + " 23:59:59'  " + sqlWhere + ") t1  WHERE  row_num=1 and yhly=" + roletype + " and (create_on) " +
                  "  between '" + startTime + "' and '" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) ) " +
                  " t   left join zm_user_cj m  on t.create_by=m.\"number\"  order by village desc,t.create_on desc) t where 1=1 and jsrysstd='是' " + where;
            }
            else if (filed == "wdbcount")
            {
                sql_count = @" select count(1) cnt   from(SELECT t.*,nickname  FROM(select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,hzxm,address,fwbh,cs,create_on,create_name,create_by,jsrysstd, case when yhly='3' then '区级核验' when yhly='2' then '市级核验'   else '乡镇核验' end yhly from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
                  "  m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='" + roletype + "' and (m1.create_on) " +
                  "  between '" + startTime + "' and '" + endTime + " 23:59:59'  " + sqlWhere + ") t1  WHERE  row_num=1 and yhly=" + roletype + " and (create_on) " +
                  "  between '" + startTime + "' and '" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) ) " +
                  " t   left join zm_user_cj m  on t.create_by=m.\"number\"  order by village desc,t.create_on desc) t where 1=1 and (jsrysstd ='否' OR jsrysstd is null ) " + where ;

            }
            else if (filed == "yhcount")
            {
                sql_count = "select count(1) cnt from (SELECT  m1.xzqdm,m1.xzq,m1.town towndm,m1.towndm town,m1.village villagedm,m1.villagedm village,hzxm,address,m1.fwbh ,cs ,m1.create_on," +
                   "check_id,check_name,check_sm,check_by,t3.nickname,case when zg_value=1 then '已整改' else '未整改' end zg_value,case when jczt='2' then '已复合通过' when jczt='1' then '待复核' else '未复核'" +
                   " end jczt,case when jcly='3' then '区级核验' when jcly='2' then '市级核验'   else '乡镇核验' end yhly   FROM zm_pc_yhlog_dx_sqjz m1 left join zm_pc_base t2 on m1.fwbh=t2.fwbh left join zm_user_cj t3 on m1.check_by=t3.\"number\"  WHERE m1.create_on between '" + startTime + "' " +
                   "and '" + endTime + " 23:59:59'    " + sqlWhere + "  AND jcly = " + roletype + " and m1.fwbh not in(select fwbh from ( select ROW_NUMBER() OVER(PARTITION BY fwbh ORDER BY create_on DESC) AS row_num,fwbh,fwzt " +
                   "   from zm_pc_baselog_cj where  (create_on)<= '" + endTime + " 23:59:59' " + strWhere + ") t where row_num =1 and  fwzt is not null )" +
                   "  and m1.fwbh not in(select fwbh from ( select ROW_NUMBER() OVER(PARTITION BY fwbh ORDER BY  create_on DESC) AS row_num,fwbh,fwzt  from zm_pc_baselog_sqjz where  " +
                   "yhly='4' and (create_on)<= '" + endTime + " 23:59:59' " + strWhere + ") t where row_num =1 and  fwzt is not null ) order by villagedm, create_on desc) t where 1=1  " + where;
            }
            else if (filed == "zgcount")
            {
                sql_count = "select count(1) cnt  from (SELECT  m1.xzqdm,m1.xzq,m1.town towndm,m1.towndm town,m1.village villagedm,m1.villagedm village,hzxm,address,m1.fwbh ,cs ,m1.create_on," +
                    "check_id,check_name,check_sm,check_by,t3.nickname,case when zg_value=1 then '已整改' else '未整改' end zg_value,case when jczt='2' then '已复合通过' when jczt='1' then '待复核' else '未复核'" +
                    " end jczt,case when jcly='3' then '区级核验' when jcly='2' then '市级核验'   else '乡镇核验' end yhly   FROM zm_pc_yhlog_dx_sqjz m1 left join zm_pc_base t2 on m1.fwbh=t2.fwbh left join zm_user_cj t3 on m1.check_by=t3.\"number\"  WHERE m1.create_on between '" + startTime + "' " +
                    "and '" + endTime + " 23:59:59'    " + sqlWhere + "  AND jcly = " + roletype + " and m1.fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) order by villagedm, create_on desc) t where 1=1  and zg_value='已整改'  " + where;
            }
            else if (filed == "sycount")
            {
                sql_count = "select count(1) cnt  from (SELECT  m1.xzqdm,m1.xzq,m1.town towndm,m1.towndm town,m1.village villagedm,m1.villagedm village,hzxm,address,m1.fwbh ,cs ,m1.create_on," +
                    "check_id,check_name,check_sm,check_by,t3.nickname,case when zg_value=1 then '已整改' else '未整改' end zg_value,case when jczt='2' then '已复合通过' when jczt='1' then '待复核' else '未复核'" +
                    " end jczt,case when jcly='3' then '区级核验' when jcly='2' then '市级核验'   else '乡镇核验' end yhly   FROM zm_pc_yhlog_dx_sqjz m1 left join zm_pc_base t2 on m1.fwbh=t2.fwbh left join zm_user_cj t3 on m1.check_by=t3.\"number\"  WHERE m1.create_on between '" + startTime + "' " +
                    "and '" + endTime + " 23:59:59'    " + sqlWhere + "  AND jcly = " + roletype + " and m1.fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) order by villagedm, create_on desc) t where 1=1  and zg_value='未整改'  " + where ;
            }


            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }

        /// <summary>
        /// 隐患统计分析————根据行政区划统计隐患情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("hc/yhstatic/hystaticlist")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<object>>), 200)]
        public RespRet<Page<object>> YhStaticListBydate([FromQuery]  string yhly, [FromQuery]  string startime, [FromQuery] string endtime, [FromQuery] int pageindex , [FromQuery] int  pagesize)
        {
            
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq xzq = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);
            string strWhere = "  ";
            //if (yhly != null)
            //{
            //    user.RoleType = (short)yhly;
            //}
            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)
            {
                strWhere += " and xzqdm='" + xzq.RegionCode + "'";
            }
            string role_type = "";
            if (!string.IsNullOrEmpty(yhly)) 
            {
                role_type = yhly;
            }
            string toDay = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endtime))
            {
                DateTime endData = Convert.ToDateTime(endtime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(toDay);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = @$"select ROW_NUMBER() OVER ()index ,* from (select sum(sfczckfhwggptzl)sfczckfhwggptzl,
	                                sum(slljdx)slljdx,
	                                sum(ygbjwfwhyx)ygbjwfwhyx,
	                                sum(use_stacked_items)use_stacked_items,
	                                sum(mhqwbcwhyx)mhqwbcwhyx,
	                                sum(use_electric_bicycle)use_electric_bicycle,
	                                sum(use_bottled_liquefied_gas)use_bottled_liquefied_gas,
	                                sum(jyplmhxtwbcwhyx)jyplmhxtwbcwhyx,
	                                sum(sfczckfhwggptzl_sycount)sfczckfhwggptzl_sycount,
	                                sum(slljdx_sycount)slljdx_sycount,
	                                sum(ygbjwfwhyx_sycount)ygbjwfwhyx_sycount,
	                                sum(use_stacked_items_sycount)use_stacked_items_sycount,
	                                sum(mhqwbcwhyx_sycount)mhqwbcwhyx_sycount,
	                                sum(use_electric_bicycle_sycount)use_electric_bicycle_sycount,
	                                sum(use_bottled_liquefied_gas_sycount)use_bottled_liquefied_gas_sycount,
	                                sum(jyplmhxtwbcwhyx_sycount)jyplmhxtwbcwhyx_sycount,
	                                sum(sfczckfhwggptzl_zgcount)sfczckfhwggptzl_zgcount,
	                                sum(slljdx_zgcount)slljdx_zgcount,
	                                sum(ygbjwfwhyx_zgcount)ygbjwfwhyx_zgcount,
	                                sum(use_stacked_items_zgcount)use_stacked_items_zgcount,
	                                sum(mhqwbcwhyx_zgcount)mhqwbcwhyx_zgcount,
	                                sum(use_electric_bicycle_zgcount)use_electric_bicycle_zgcount,
	                                sum(use_bottled_liquefied_gas_zgcount)use_bottled_liquefied_gas_zgcount,
	                                sum(jyplmhxtwbcwhyx_zgcount)jyplmhxtwbcwhyx_zgcount,
	                                sum(yhtotal) yhtotal,
	                                sum(zgtotal) zgtotal,
	                                sum(sytotal) sytotal,xzq,xzqdm from(" +
                               " select* from pro_yh_list('"+startime+"','"+endtime+ " 23:59:59','"+ role_type + "','zm_pc_yhlog_dx_sqjz','zm_pc_yhlog_dxzgson_sqjz','" + table_fwzt+"')"+
                               " union all" +
                               " select * from pro_yh_list('"+ startime + "','"+ endtime + " 23:59:59','"+ role_type + "','zm_pc_yhlog_dx_cj','zm_pc_yhlog_dxzgson_cj','" + table_fwzt + "'))t group by xzq,xzqdm order by xzqdm) t where 1=1 "+strWhere;
            ;
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = list.Count;
            var pageData = new Page<object>(list
                , pageindex + 1, pagesize, count);
            return new RespRet<Page<object>>(true, "Ok", pageData);
        }

        /// <summary>
        /// 隐患统计分析————根据乡镇统计隐患情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("hc/yhstatic/HyTownshipYhstaticlist")]
        public RespRet<Page<object>> TownshipYhstaticlistBydate([FromQuery] string xzqdm, [FromQuery] string yhly, string startime, string endtime,[FromQuery] int pageindex, [FromQuery] int pagesize)
        {
            if (startime == null || endtime == null)
            {
                return new RespRet<Page<object>>(false, "请选择时间");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);
            string strWhere = "";

            if (user.RoleType == 3)
            {
                strWhere += " and xzqdm='" + model.RegionCode + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            string role_type = "";
            if (!string.IsNullOrEmpty(yhly))
            {
                role_type = yhly;
            }
            string toDay = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endtime))
            {
                DateTime endData = Convert.ToDateTime(endtime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(toDay);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = @"  select ROW_NUMBER() OVER ()index ,* from (select sum(sfczckfhwggptzl)sfczckfhwggptzl,
                                    sum(slljdx)slljdx,
	                                sum(ygbjwfwhyx)ygbjwfwhyx,
	                                sum(use_stacked_items)use_stacked_items,
	                                sum(mhqwbcwhyx)mhqwbcwhyx,
	                                sum(use_electric_bicycle)use_electric_bicycle,
	                                sum(use_bottled_liquefied_gas)use_bottled_liquefied_gas,
	                                sum(jyplmhxtwbcwhyx)jyplmhxtwbcwhyx,
	                                sum(sfczckfhwggptzl_sycount)sfczckfhwggptzl_sycount,
	                                sum(slljdx_sycount)slljdx_sycount,
	                                sum(ygbjwfwhyx_sycount)ygbjwfwhyx_sycount,
	                                sum(use_stacked_items_sycount)use_stacked_items_sycount,
	                                sum(mhqwbcwhyx_sycount)mhqwbcwhyx_sycount,
	                                sum(use_electric_bicycle_sycount)use_electric_bicycle_sycount,
	                                sum(use_bottled_liquefied_gas_sycount)use_bottled_liquefied_gas_sycount,
	                                sum(jyplmhxtwbcwhyx_sycount)jyplmhxtwbcwhyx_sycount,
	                                sum(sfczckfhwggptzl_zgcount)sfczckfhwggptzl_zgcount,
	                                sum(slljdx_zgcount)slljdx_zgcount,
	                                sum(ygbjwfwhyx_zgcount)ygbjwfwhyx_zgcount,
	                                sum(use_stacked_items_zgcount)use_stacked_items_zgcount,
	                                sum(mhqwbcwhyx_zgcount)mhqwbcwhyx_zgcount,
	                                sum(use_electric_bicycle_zgcount)use_electric_bicycle_zgcount,
	                                sum(use_bottled_liquefied_gas_zgcount)use_bottled_liquefied_gas_zgcount,
	                                sum(jyplmhxtwbcwhyx_zgcount)jyplmhxtwbcwhyx_zgcount,
	                                sum(yhtotal) yhtotal,
	                                sum(zgtotal) zgtotal,
	                                sum(sytotal) sytotal,xzq,xzqdm,town,towndm from(" +
                               " select* from pro_yh_xz_list('" + startime + "','" + endtime + " 23:59:59','"+role_type+"','zm_pc_yhlog_dx_sqjz','zm_pc_yhlog_dxzgson_sqjz','"+xzqdm+"','" + table_fwzt + "')" +
                               " union all" +
                               " select * from pro_yh_xz_list('" + startime + "','" + endtime + " 23:59:59','"+ role_type + "','zm_pc_yhlog_dx_cj','zm_pc_yhlog_dxzgson_cj','"+ xzqdm + "','" + table_fwzt + "'))t" +
                               " group by xzq,xzqdm,town,towndm order by towndm) t where 1=1 " + strWhere;
            ;
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select count(1) cnt from (select * from(" +
                               " select* from pro_yh_xz_list('" + startime + "','" + endtime + " 23:59:59','" + role_type + "','zm_pc_yhlog_dx_sqjz','zm_pc_yhlog_dxzgson_sqjz','" + xzqdm + "','" + table_fwzt + "')" +
                               " union all" +
                               " select * from pro_yh_xz_list('" + startime + "','" + endtime + " 23:59:59','" + role_type + "','zm_pc_yhlog_dx_cj','zm_pc_yhlog_dxzgson_cj','" + xzqdm + "','" + table_fwzt + "'))t" +
                               " group by xzq,xzqdm,town,towndm order by towndm) t where 1=1 " + strWhere;
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = datas.Count();

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 隐患统计分析————根据村级统计隐患情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("hc/yhstatic/HyVillageYhstaticlist")]
        public RespRet<Page<object>> VillageYhstaticlistBydate([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string yhly, string startime, string endtime)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }
            if (startime == null || endtime == null)
            {
                return new RespRet<Page<object>>(false, "请选择时间");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            string strWhere = "";
            if (!string.IsNullOrEmpty(xzqdm + ""))
            {
                strWhere += "and xzqdm ='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(towndm + ""))
            {
                strWhere += "and towndm ='" + towndm + "'";
            }
            string role_type = "";
            if (!string.IsNullOrEmpty(yhly))
            {
                role_type = yhly;
            }
            string toDay = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endtime))
            {
                DateTime endData = Convert.ToDateTime(endtime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(toDay);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = @"  select ROW_NUMBER() OVER ()index ,* from (select sum(sfczckfhwggptzl)sfczckfhwggptzl,
                                    sum(slljdx)slljdx,
	                                sum(ygbjwfwhyx)ygbjwfwhyx,
	                                sum(use_stacked_items)use_stacked_items,
	                                sum(mhqwbcwhyx)mhqwbcwhyx,
	                                sum(use_electric_bicycle)use_electric_bicycle,
	                                sum(use_bottled_liquefied_gas)use_bottled_liquefied_gas,
	                                sum(jyplmhxtwbcwhyx)jyplmhxtwbcwhyx,
	                                sum(sfczckfhwggptzl_sycount)sfczckfhwggptzl_sycount,
	                                sum(slljdx_sycount)slljdx_sycount,
	                                sum(ygbjwfwhyx_sycount)ygbjwfwhyx_sycount,
	                                sum(use_stacked_items_sycount)use_stacked_items_sycount,
	                                sum(mhqwbcwhyx_sycount)mhqwbcwhyx_sycount,
	                                sum(use_electric_bicycle_sycount)use_electric_bicycle_sycount,
	                                sum(use_bottled_liquefied_gas_sycount)use_bottled_liquefied_gas_sycount,
	                                sum(jyplmhxtwbcwhyx_sycount)jyplmhxtwbcwhyx_sycount,
	                                sum(sfczckfhwggptzl_zgcount)sfczckfhwggptzl_zgcount,
	                                sum(slljdx_zgcount)slljdx_zgcount,
	                                sum(ygbjwfwhyx_zgcount)ygbjwfwhyx_zgcount,
	                                sum(use_stacked_items_zgcount)use_stacked_items_zgcount,
	                                sum(mhqwbcwhyx_zgcount)mhqwbcwhyx_zgcount,
	                                sum(use_electric_bicycle_zgcount)use_electric_bicycle_zgcount,
	                                sum(use_bottled_liquefied_gas_zgcount)use_bottled_liquefied_gas_zgcount,
	                                sum(jyplmhxtwbcwhyx_zgcount)jyplmhxtwbcwhyx_zgcount,
	                                sum(yhtotal) yhtotal,
	                                sum(zgtotal) zgtotal,
	                                sum(sytotal) sytotal,xzq,xzqdm,town,towndm,villagedm,village from(" +
                               " select* from pro_yh_cj_list('" + startime + "','" + endtime + " 23:59:59','" + role_type + "','zm_pc_yhlog_dx_sqjz','zm_pc_yhlog_dxzgson_sqjz','" + xzqdm + "','"+towndm+"','" + table_fwzt + "')" +
                               " union all" +
                               " select * from pro_yh_cj_list('" + startime + "','" + endtime + " 23:59:59','" + role_type + "','zm_pc_yhlog_dx_cj','zm_pc_yhlog_dxzgson_cj','" + xzqdm + "','"+ towndm + "','" + table_fwzt + "'))t" +
                               " group by xzq,xzqdm,town,towndm,villagedm,village order by villagedm) t where 1=1 " + strWhere;
            ;
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
               || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
               || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = " select count(1) cnt from (select * from(" +
                               " select* from pro_yh_xz_list('" + startime + "','" + endtime + " 23:59:59','" + role_type + "','zm_pc_yhlog_dx_sqjz','zm_pc_yhlog_dxzgson_sqjz','" + xzqdm + "','" + table_fwzt + "')" +
                               " union all" +
                               " select * from pro_yh_xz_list('" + startime + "','" + endtime + " 23:59:59','" + role_type + "','zm_pc_yhlog_dx_cj','zm_pc_yhlog_dxzgson_cj','" + xzqdm + "','" + table_fwzt + "'))t" +
                               " group by xzq,xzqdm,town,towndm order by towndm) t where 1=1 " + strWhere;

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = datas.Count();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });


        }

        /// <summary>
        /// 根据某个隐患项获取此隐患的房屋
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("hc/yhstatic/hyyhdetaile")]
        public RespRet<Page<object>> yhdetaileByDate([FromQuery] string check_id, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string txtKeyWord, [FromQuery] string yhly, [FromQuery] string startime, [FromQuery] string endtime, [FromQuery] string zgvalue, [FromQuery] string type)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }
          
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            string strWhere = "";
            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(towndm))
            {
                strWhere += " and (towndm='" + towndm + "' or town='" + towndm + "')";
            }
            if (!string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and (villagedm='" + villagedm + "' or village='" + villagedm + "')";
            }
            if (string.IsNullOrEmpty(startime) && string.IsNullOrEmpty(endtime))
            {
                startime = "" + startime + "";
                endtime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            if (type == "zg" )
            {
                strWhere += " and zgvalue='已整改'";
            }
            else if (type == "sy")
            {
                strWhere += " and zgvalue='未整改'";
            }
            else if (type == "yh")
            {
                strWhere += " and create_on BETWEEN '" + startime + "' and '" + endtime + " 23:59:59' ";
            }
            if (check_id != "sytotal" && check_id != "zgtotal" && check_id != "yhtotal")
            {
                strWhere += " and check_id='"+ check_id + "'";
            }
          
            if (!string.IsNullOrEmpty(zgvalue)) 
            {
                strWhere += " and zgvalue='" + zgvalue + "'";
            }
            string toDay = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endtime))
            {
                DateTime endData = Convert.ToDateTime(endtime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(toDay);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            if (!string.IsNullOrEmpty(yhly))
            {
                strWhere += " and jcly ='" + yhly + "'";
            }
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere+=" and (hzxm like '%"+ txtKeyWord + "%' OR address like '%"+ txtKeyWord + "%' OR fwbh like '%"+ txtKeyWord + "%' ) ";
            }
            string sql = "";
            sql = @$"  select ROW_NUMBER() OVER ()index, * from (
         select  xzq,xzqdm,towndm town,town towndm, villagedm village,village villagedm,hzxm,address,fwbh,check_id,check_name,create_on,case when jcly=3 then '区级核验' when jcly=4 then 
        '乡镇核验' when jcly=2 then '市级核验' end yhly,create_name,zgvalue from (select distinct * from (select t1.id, t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village 
        villagedm,t1.villagedm  village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on,t1.check_id,t1.check_name,t1.jcly,t1.check_nickname create_name,case when zg_value=1 and t1.id in (select yhlog_dx_id from zm_pc_yhlog_dxzgson_sqjz where zg_time  between '" + startime + "' and '" + endtime + " 23:59:59')  then  '已整改' else '未整改' end zgvalue  from zm_pc_yhlog_dx_sqjz t1   left join zm_buildings t2 on t1.fwbh=t2.fwbh) t2) t where fwbh not in(select distinct fwbh from " + table_fwzt +
      "  where final_fwzt is not null and final_create_on='" + endtime + " 23:59:59')  " + strWhere +
      "       union all     " +
      "    select xzq,xzqdm,towndm town,town towndm, villagedm village,village villagedm,hzxm,address,fwbh,check_id,check_name,create_on,'村级核验'  yhly,create_name,zgvalue from " +
      "  (select distinct *  from (select t1.id, t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value," +
      "  t1.create_on,t1.check_name, t1.jcly,t1.check_id,t1.check_nickname create_name,case when zg_value=1 and t1.id in (select yhlog_dx_id from zm_pc_yhlog_dxzgson_cj where zg_time  BETWEEN '2024-01-01' and '" + endtime + " 23:59:59')  then  '已整改' else '未整改' end zgvalue from zm_pc_yhlog_dx_cj t1 " +
      " left join zm_buildings t2 on t1.fwbh=t2.fwbh) zm_buildings ) t2  where fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and " +
      "  final_create_on='" + endtime + " 23:59:59')   " + strWhere +
      "   ) t where 1=1      "
+ "  " + "  limit " + pagesize + " offset(" + (pageindex - 1) + ") * " + pagesize;
            string sql_count = @$"  select count(1) cnt from (
         select  xzq,xzqdm,towndm town,town towndm, villagedm village,village villagedm,hzxm,address,fwbh,check_id,check_name,create_on,case when jcly=3 then '区级核验' when jcly=4 then 
        '乡镇核验' when jcly=2 then '市级核验' end yhly,create_name,zgvalue from (select distinct * from (select t1.id, t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village 
        villagedm,t1.villagedm  village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on,t1.check_id,t1.check_name,t1.jcly,t1.check_nickname create_name,case when zg_value=1 and t1.id in (select yhlog_dx_id from zm_pc_yhlog_dxzgson_sqjz where zg_time  between '" + startime + "' and '" + endtime + " 23:59:59')  then  '已整改' else '未整改' end zgvalue  from zm_pc_yhlog_dx_sqjz t1   left join zm_buildings t2 on t1.fwbh=t2.fwbh) t2) t where fwbh not in(select distinct fwbh from " + table_fwzt +
      "  where final_fwzt is not null and final_create_on='" + endtime + " 23:59:59')  " + strWhere +
      "       union all     " +
      "    select xzq,xzqdm,towndm town,town towndm, villagedm village,village villagedm,hzxm,address,fwbh,check_id,check_name,create_on,'村级核验'  yhly,create_name,zgvalue from " +
      "  (select distinct *  from (select t1.id, t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value," +
      "  t1.create_on,t1.check_name, t1.jcly,t1.check_id,t1.check_nickname create_name,case when zg_value=1 and t1.id in (select yhlog_dx_id from zm_pc_yhlog_dxzgson_cj where zg_time  between '" + startime + "' and '" + endtime + " 23:59:59')  then  '已整改' else '未整改' end zgvalue from zm_pc_yhlog_dx_cj t1 " +
      " left join zm_buildings t2 on t1.fwbh=t2.fwbh) zm_buildings ) t2  where fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and " +
      "  final_create_on='" + endtime + " 23:59:59')   " + strWhere +
      "   ) t where 1=1    ";


            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
              || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
              || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });


        }
    }
}







